package com.zkq.graduate.util;

import com.google.common.collect.Lists;
import com.zkq.graduate.pojo.Colleage;
import com.zkq.graduate.pojo.Major;
import com.zkq.graduate.pojo.Role;
import com.zkq.graduate.pojo.University;
import com.zkq.graduate.service.ColleageService;
import com.zkq.graduate.service.MajorService;
import com.zkq.graduate.service.RoleService;
import com.zkq.graduate.service.UniversityService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;
import static org.apache.poi.ss.usermodel.CellType.STRING;

/**
 * @Author zhangkaiqiang
 * @Date 2020/1/3  14:51
 * @Description TODO
 * 导入学生信息  导入教师信息
 */
@Component
@Slf4j
public class ImportExeclUtil {

	@Resource(name = "majorService")
	private  MajorService majorService;

	@Resource(name = "universityService")
	private  UniversityService universityService;

	@Resource(name = "colleageService")
	private  ColleageService colleageService;

	@Resource(name = "roleService")
	private RoleService roleService;

	private  static ImportExeclUtil importExeclUtil;

	@PostConstruct
	public void init(){
		importExeclUtil=this;
		importExeclUtil.universityService=this.universityService;
		importExeclUtil.colleageService=this.colleageService;
		importExeclUtil.majorService=this.majorService;
		importExeclUtil.roleService=this.roleService;
	}


	private static List<Major> allMajor = new ArrayList<>();
	private static List<University> allUniversity = new ArrayList<>();
	private static List<Colleage> allColleage = new ArrayList<>();


	/**
	 * 开始时加载缓存,加快从name到id的转换速度
	 */
	public static void loadCache(){
		allUniversity = importExeclUtil.universityService.findAll();
		allColleage = importExeclUtil.colleageService.findAll();
		allMajor = importExeclUtil.majorService.findAll();
	}



	/**
	 * 进行文件的校验
	 * @param file
	 * @return
	 */
	public static Map<String,Object> checkFile(MultipartFile file){
		Map<String,Object> checkResult=new HashMap<>(16);
		if(file==null ||  file.getSize()==0){
			log.error("导入的Excel不存在或者文件内容为空");
			checkResult.put("code",404);
			checkResult.put("msg","导入的文件不存在");
		}

		String fileName=file.getOriginalFilename();
		if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
			log.error("上传的文件不是Excel文件");
			checkResult.put("code",500);
			checkResult.put("msg","导入的文件不是excel文件");
		}else{
			checkResult.put("code",0);
			checkResult.put("msg","success");
		}
		return checkResult;
	}


	/**
	 * 从Excel中得到一个sheet,如果获得的sheet为null则返回一个空的sheet
	 *
	 * @param file  上传的文件
	 * @param index 第几个sheet
	 * @return
	 */
	public static XSSFSheet getSheet(int index, MultipartFile file) {
		XSSFWorkbook wordBook = null;
		try {
			InputStream inputStream = file.getInputStream();
			// 读取工作簿
			wordBook = new XSSFWorkbook(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
		XSSFSheet sheet = wordBook.getSheetAt(index);
		return sheet!=null?sheet:wordBook.createSheet();
	}


	/**
	 * 得到sheet中的数据--包含表格的标题
	 *
	 * @param sheet
	 * @return List<Map<String,String> 每一行数据对应一个String[],把所有的数据加入到List中
	 */
	public static List<Map<String,String>> getSheetData(XSSFSheet sheet) {
		if (sheet == null) {
			throw new RuntimeException("sheet 不能为null");
		}
		List<Map<String,String>> list = new LinkedList<>();
		// 记录的第一行与总行数
		int firstRow=sheet.getFirstRowNum();
		int rowSize = sheet.getLastRowNum();

		// 得到第一行的表头
		Row row=sheet.getRow(firstRow);
		int columns = row.getLastCellNum();
		String[] titles=new String [columns];
		for(int x=0;x<columns;x++){
			Cell cell = row.getCell(x);
			titles[x]=getCellValue(cell);
		}

		// 循环数据部分,填充到Map中
		for (int i = firstRow+1; i <= rowSize; i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			// 循环一行的列
			int firstColumns=row.getFirstCellNum();
			// 每一行对应的数据
			Map<String,String> data=new LinkedHashMap<>();
			for (int j = firstColumns; j < columns; j++) {
				Cell cell = row.getCell(j);
				data.put(titles[j],getCellValue(cell));
			}
			list.add(data);
		}
		return list;
	}


	/**
	 * 根据单元格数据格式的不同得到单元格的数据
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell){
		String cellValue = "";
		if(cell == null){
			return cellValue;
		}
		//把数字当成String来读，避免出现1读成1.0的情况
		if(cell.getCellType() == NUMERIC){
			cell.setCellType(STRING);
		}
		//判断数据的类型
		switch (cell.getCellType()){
			// 数字
			case NUMERIC:
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
				// 字符串
			case STRING:
				cellValue = String.valueOf(cell.getStringCellValue());
				break;
				// boolean
			case BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
				// 空值
			case BLANK:
				cellValue = "";
				break;
				// 非法字符
			case ERROR:
				cellValue = "非法字符";
				break;
			default:
				cellValue = "未知类型";
				break;
		}
		return cellValue;
	}


	/**
	 * 把字段的名称映射为字段的id
	 * @return 字段转换后的数据
	 * @param data 原始数据
	 */
	public static List<Map<String,String>> nameToValue(List<Map<String,String>> data,String flag){
		if(CollectionUtils.isEmpty(data) ){
			return Lists.newArrayList();
		}

		loadCache();
		List<Role> roles=importExeclUtil.roleService.listAllRole();
		data.forEach((item)->{
			String universityName=item.getOrDefault("所属学校","");
			String colleageName=item.getOrDefault("所属学院","");
			String majorName=item.getOrDefault("所属专业","");

			// 进行学号/教师工号  姓名 手机号字段名的替换
			if(Objects.equals(flag,"student")){
				item.put("account",item.getOrDefault("学号",""));
			} else if(Objects.equals(flag,"teacher")){
				item.put("account",item.getOrDefault("教师工号",""));
			}

			// 进行角色id的填充
			roles.forEach((role)->{
				if(Objects.equals(flag,role.getRoleName())){
					item.put("role_id",String.valueOf(role.getId()));
				}
			});
			item.put("name",item.getOrDefault("姓名",""));
			item.put("phone",item.getOrDefault("手机号",""));

			// 进行密码的填充
			String lastPhone=item.getOrDefault("手机号","");
			if(lastPhone.length()==11){
				String simplePss= StringUtils.substring(lastPhone,5);
				String password=MD5Util.mD5Encode(simplePss);
				item.put("password",password);
			}

			// 替换学校id
			allUniversity.forEach((un)->{
				if(Objects.equals(un.getUniversityName(),universityName)){
					item.put("university_id",String.valueOf(un.getId()));
				}
			});

			// 替换学院id
			allColleage.forEach((co)->{
				if(Objects.equals(co.getColleageName(),colleageName) && Objects.equals(String.valueOf(co.getUniversity().getId()),item.get("university_id"))){
					item.put("college_id",String.valueOf(co.getId()));
				}
			});

			// 替换专业id
			allMajor.forEach((ma)->{
				if(Objects.equals(ma.getMajorName(),majorName) && Objects.equals(String.valueOf(ma.getCollege().getId()),item.get("college_id")) && Objects.equals(String.valueOf(ma.getUniversity().getId()),item.get("university_id"))){
					item.put("major_id",String.valueOf(ma.getId()));
				}
			});

			item.remove("姓名");
			item.remove("手机号");
			item.remove("学号");
		});

		return data;
	}

	public MajorService getMajorService() {
		return majorService;
	}

	public void setMajorService(MajorService majorService) {
		this.majorService = majorService;
	}

	public UniversityService getUniversityService() {
		return universityService;
	}

	public void setUniversityService(UniversityService universityService) {
		this.universityService = universityService;
	}

	public ColleageService getColleageService() {
		return colleageService;
	}

	public void setColleageService(ColleageService colleageService) {
		this.colleageService = colleageService;
	}

	public static ImportExeclUtil getImportExeclUtil() {
		return importExeclUtil;
	}

	public static void setImportExeclUtil(ImportExeclUtil importExeclUtil) {
		ImportExeclUtil.importExeclUtil = importExeclUtil;
	}

	public static List<Major> getAllMajor() {
		return allMajor;
	}

	public static void setAllMajor(List<Major> allMajor) {
		ImportExeclUtil.allMajor = allMajor;
	}

	public static List<University> getAllUniversity() {
		return allUniversity;
	}

	public static void setAllUniversity(List<University> allUniversity) {
		ImportExeclUtil.allUniversity = allUniversity;
	}

	public static List<Colleage> getAllColleage() {
		return allColleage;
	}

	public static void setAllColleage(List<Colleage> allColleage) {
		ImportExeclUtil.allColleage = allColleage;
	}
}